<?php

/**
 * User: PuLinGao
 * Date: 2015/01/04
 * 处理问卷答案导入Model
 */
class QuestionAnswerImportModel {

	private $question_id;
	private $project_id;
	private $subject_type;
	private $answerInduceTitle;
	private $questionDetail;
	private $questionNetwork;
	private $buffer;
	private $subjectTable;
	private $subjectNames;
	private $error;

	// 防止执行时间较长
	public function __construct() {
		ini_set("max_execution_time", 0);
		ini_set("memory_limit", "2048M");
	}

	//回答题型中，必须出现的表头信息
	public static $mustThField = array(
		'th_score' => '得分',
		'th_check_desc' => '情景描述',
		'th_option_content' => '选项值'
	);
	
	//模板中应存在的合法标题
	public static function legalField() {
		$mustTh = self::$mustThField;
		$other = array(
			'legal_question_name' => '问卷名称',
			'legal_branch_name' => '网点编号',
			'legal_branch_num' => '网点名称'
		);
		return array_merge($mustTh, $other);
	}

	//模板中如果存在网点编号，网点名称等必填操作，则同时验证网点编号和网点名称两项
	public static $moreoverField = array(
		'network_num' => '网点编号',
		'network_name' => '网点名称',
//		'branch_num' => '分行编号',
//		'branch_name' => '分行名称'
	);
	
	/**
	 * 设置错误信息，方便提示上传者
	 * @param type $errNo 错误单元格编号
	 * @param type $errMsg 错误信息
	 */
	private function setError($errNo, $errMsg) {
		$this->error[] = array(
			'err_no' => $errNo,
			'err_msg' => $errMsg
		);
	}

	/**
	 * 获取错误信息
	 * @return type
	 */
	public function getError() {
		return $this->error;
	}

	/**
	 * 格式化excel数据
	 * @param type $question_id 问卷ID
	 * @param type $excelData	问卷excel数据
	 * @return boolean
	 */
	public function formatExcelData($question_id, $excelData) {
		if (!$question_id) {
			throw new Exception("请指定问卷");
		}
		if (empty($excelData)) {
			throw new Exception("未获取到excel信息");
		}
		# 检测问卷内容是否合法
		$legal = $this->checkLegal($excelData);
		if (!$legal['ok']) {
			throw new Exception($legal['msg']);
		}

		$this->question_id = $question_id;

		//基础数据
		$this->subjectTable = QuestionSubject::model()->getListByBase($this->question_id);
		$this->subjectNames = $this->subjectCate();
		$this->questionDetail = QuestionBase::model()->find('question_id=' . $this->question_id);
		$this->project_id = $this->questionDetail->project_id;
		$this->questionNetwork = NetworkQuestion::model()->getQuestionNetwork($this->question_id, $this->project_id);
		
		//提取和分类数据库中的数据 
		$induceExcelData = $this->induceData($excelData);
		
		//答案合格性检测
		foreach ($induceExcelData as $key => $data) {

			//基础题目检测
			switch ($key) {
				case "base":
					$tmpMoreOver = array();
					foreach ($data as $one) {
						$checkBase = $this->checkBase($one);
						$moreoverKey = array_search($one['data_th']['title'], self::$moreoverField);
						if ($moreoverKey) {
							$tmpMoreOver[$moreoverKey] = $one['data'];
						}
					}
					
					if ($tmpMoreOver) {
						$this->checkMoreOver($tmpMoreOver);
					}
					
					if ($this->subjectNames['base']) {
						foreach ($this->subjectNames['base'] as $skey => $sdata) {
							if ($this->subjectTable[$skey]['extend']['is_required']) {
								$this->setError('warning', '问卷题目“' . $sdata . '”在上传的文件中未找到对应答案！');
							}
						}
					}
					break;

				case "formal":
					$checkFormal = $this->checkFormal($data);
					break;

				default:
					break;
			}
		}
		
		if ($this->subjectNames['formal']) {
			foreach ($this->subjectNames['formal'] as $skey => $sdata) {
				if ($this->subjectTable[$skey]['extend']['is_required']) {
					$this->setError('warning', '问卷题目“' . $skey . '”在上传的文件中未找到对应答案！');
				}
			}
		}
	}

	/**
	 * 保存数据，数据入库
	 */
	public function saveExcelData() {
		$networkQuestion = new NetworkQuestion();
		$status = true;
		$scoreTotal = array();
		$completeNumTotal = array();
		
		foreach ($this->buffer as $key => $data) {
			$nq_id = $data['nq']['nq_id'];
			$completeNumTotal[$nq_id] = count($data['subject']);		//20150426需求，导入问卷回答时更新complete_num字段（默认是取当前excel中不同nq_id中包含的所有题目的数量）
			
			# 更新network_question_answer
			$datas = array();
			foreach ($data['subject'] as $chrKey => $chrData) {
				
				//首先删除一些存在的记录
				$subject_id = $chrData['subject_id'];
				$this->handleRelationRecord($nq_id, $subject_id);
				
				// 存在标准得分，且标准得分是数字的才计算回答的得分
				if (is_numeric($this->subjectTable[$chrKey]['standard_score'])) {
					if (!isset($scoreTotal[$nq_id])) {
						$scoreTotal[$nq_id] = 0;
					}
					$scoreTotal[$nq_id] += $chrData['answer_score'];
				}
				$chrData['nq_id'] = $nq_id;
				$chrData['question_id'] = $this->question_id;		# 20150506需求，添加问卷回答时加入question_id
				$datas[] = $chrData;
			}
			
			$addStatus = NetworkQuestionAnswer::model()->addAnswer($datas);
			$status = $status && $addStatus;

			# 更新问卷总得分
			if ($addStatus) {
                            $score = number_format($scoreTotal[$nq_id], 2);
                            $networkQuestion->updateByPk($nq_id, array('score' => $score, 'complete_num' => $completeNumTotal[$nq_id]));		//20150426需求“'complete_num' => $completeNumTotal[$nq_id]”更新完成网点问卷完成问题总数
			}
		}
		
		return $status;
	}

	/**
	 * 在每次导入之前，先更新或者删除一些关联的记录
	 * @param type $nq_id			network_question 的自增ID
	 * @param type $subject_id		question_subject 的自增ID
	 * @return boolean
	 */
	protected function handleRelationRecord($nq_id, $subject_id) {
		if (!$nq_id || !$subject_id) {
			return false; 
		}
		
		# 1.更新network_question中的audit和status字段为默认值
		$upData = array(
			'audit' => 0,
			'status' => 1
		);
		NetworkQuestion::model()->updateByPk($nq_id, $upData);
		
		/**
		 * 2.依次删除 
		 * 虽然表中使用了subject_id用来区分不同的题目，
		 * 但是这里重新上传时需要删除所有的nq_id的记录，因为nq_id是自增的【每次在添加或者修改问卷时，往network_question中插入数据时，都是先删除所有相同问卷ID的网点信息，再插入】，
		 * 因此直接删除所有表中存在nq_id的记录就可以了
		 * 
		 * network_question_attach, 
		 * question_benchmark, 
		 * question_benchmark_attach, 
		 * question_benchmark_manage  中 $nq_id、$subject_id对应的记录
		 */
		$delModels = array(
			'nqa_id' => new NetworkQuestionAttach(),
			'qb_id' => new QuestionBenchmark(),
			'qba_id' => new QuestionBenchmarkAttach(),
			'qbm_id' => new QuestionBenchmarkManage(),
		);
		$condition = 'nq_id=:nq_id';
		$params = array(
			':nq_id' => $nq_id
		);
		foreach ($delModels as $mKey => $mObj) {
			$count = $mObj->count($condition, $params);
			if ($count) {
				# 删除
				$delSta = $mObj->deleteAll($condition, $params);
			}
		}
	}
	
	/**
	 * 正式解析数据之前，验证用户上传的是否是按照规定的模板生成的数据
	 * @param type $excelData
	 * @return boolean
	 */
	public function checkLegal($excelData) {
		$return = array(
			'ok' => false,
			'msg' => ''
		);
		if (!$excelData['data_th'] || !is_array($excelData['data_th'])) {
			$return['msg'] = "未能成功检测到表头信息，请检查上传模板是否正确";
			return $return;
		}
		$dataTh = array();
		$tmpDataTh = array_pop($excelData['data_th']);
		foreach ($tmpDataTh as $key => $data) {
			$dataTh[] = $data['title'];
		}
		$check = false;
		foreach (self::legalField() as $value) {
			foreach ($dataTh as $data) {
				$check |= substr_count($data, $value);
			}
		}
		if (!$check) {
			$return['msg'] = "未能成功检测到必要的问卷回答表头信息，如（" . join(',', self::legalField()) . "）等，请检查上传模板是否正确";
			return $return;
		}

		return array(
			'ok' => true,
			'msg' => '合法'
		);
	}

	/**
	 * 根据数据库中的数据检测基础数据
	 * @param type $checkData
	 * @return type
	 */
	public function checkBase($checkData) {
		if (!$checkData) {
			$this->setError('warning', '未获取到基础信息数据');
		}
		// data_th 有效性检测
		$subject_key = array_search($checkData['data_th']['title'], $this->subjectNames['base']);
		if ($checkData['data_th']['title'] != '问卷名称' && !$subject_key) {
			$this->setError($checkData['data_th']['coordinate'], '“ ' . $checkData['data_th']['title'] . ' ”在问卷的内容中未找到此题目信息，请认真核对问卷');
		}
		unset($this->subjectNames['base'][$subject_key]);
		// 开始填写数据的检测
		foreach ($checkData['data'] as $i => $v) {
			if ($checkData['data_th']['title'] == '问卷名称' && $v['title'] != $this->questionDetail->question_name) { //问卷名称检测
				$this->setError($v['coordinate'], '“ ' . $checkData['data_th']['title'] . ' ”下的“' . $v['title'] . '”和原问卷名称不匹配');
			}

			//是否必填检测
			if ($this->subjectTable[$subject_key]['extend']['is_required']) {
				if ($v['title'] === '') {
					$this->setError($v['coordinate'], '“ ' . $checkData['data_th']['title'] . ' ”问卷题目规定此项必填');
				}
			}

			//因为解析时间的问题，对于基础信息中只要出现“日期”，“时间”等字样的列，都需要采用文本格式
			if (false !== strstr($checkData['data_th']['title'], '日期') || false !== strstr($checkData['data_th']['title'], '时间')) {
				if ($v['date_time_status']) {
					$this->setError($v['coordinate'], '“ ' . $checkData['data_th']['title'] . ' ” 下的“' . $v['title'] . '”，数据未能正确解析，<b style="color:red">请在excel中将单元格设置为文本格式【重要：为了解析数据的准确，请将问卷回答中所有表头带有“日期”或者“时间”下的数据单元格都设置成文本格式】</b>');
				}
			}
			
			// 刚开始这里写入了network_question表，二期变动到network_question_answer表
			$this->buffer[$i]['subject'][$subject_key] = array(
				'subject_id' => $this->subjectTable[$subject_key]['subject_id'],
				'answer_score' => 0,
				'answer_desc' => $v['title'],
				'create_time' => time()
			);
		}
	}

	/**
	 * 检测网点编号，或者分行编号 等
	 * @param type $moreover
	 */
	public function checkMoreOver($moreover) {
		$network = array();
		foreach ($this->questionNetwork as $value) {
			$network[$value['network_num']] = $value;
		}
		
		foreach ($moreover as $key => $arrs) {
			foreach ($arrs as $ckey => $data) {

				# 验证网点编号和网点名称
				if ($key == 'network_num') {
					if ($data['title'] && !preg_match('/^[0-9a-zA-Z]\w*$/i', $data['title'])) {
//						$this->setError($data['coordinate'], self::$moreoverField[$key] . '下 “' . $data['title'] . '” 格式不正确，<b>请使用字母，数字，下划线的组合，不能以下划线开头或结尾</b>');
					} else {

						# 验证网点编号是否存在
						if (!isset($network[$data['title']])) {
							$this->setError($data['coordinate'], '当前网点编号' . $data['title'] . ' 在所在问卷中未找到');
						} else {

							# 验证网点编号和网点名称是否匹配
							$curNetworks = $moreover['network_name'][$ckey];
							if ($curNetworks) { //如果不存在，说明在网点的内容导入时，没有【网点名称】的基本信息
								if ($curNetworks['title'] != $network[$data['title']]['network_name']) {
									$this->setError($curNetworks['coordinate'], '当前网点名称“' . $curNetworks['title'] . '” 与当前网点编号 “' . $data['title'] . '” 不匹配，此处的情况可能有：1.网点确实存在，但是此问卷不属于excel中填写的网点；2.确实数据库中数据不能匹配');
								}
							}
						}
					}


					# network_question 更新数据，需要提供nq_id，用来和网点对应
					$this->buffer[$ckey]['nq']['nq_id'] = $network[$data['title']]['nq_id'];
					$this->buffer[$ckey]['nq']['network_id'] = $network[$data['title']]['network_id'];
					$this->buffer[$ckey]['nq']['network_name'] = $network[$data['title']]['network_name'];
					$this->buffer[$ckey]['nq']['project_id'] = $this->project_id;
					$this->buffer[$ckey]['nq']['question_id'] = $this->question_id;
				}

				# 验证分行编号和分行名称 现有的回答模板上存在分行编号和分行名称的表头，但是没有用到验证，等到使用时再打开这里
				if ($key == 'branch_num') {
					if ($data['title'] && !preg_match('/^[0-9a-zA-Z]\w*$/i', $data['title'])) { //不验证网点的编号是否合法
//						$this->setError($data['coordinate'], self::$moreoverField[$key] . '下 “' . $data['title'] . '” 格式不正确，<b>请使用字母，数字，下划线的组合，不能以下划线开头或结尾</b>');
					} else {

						# 验证网点编号是否存在
						if (!isset($network[$data['title']])) {
							$this->setError($data['coordinate'], '模板中的分行编号“' . $data['title'] . '” 在所在问卷ID【'.$this->question_id.'】 所属的网点中未找到');
						} else {

							# 验证网点编号和网点名称是否匹配
//							$curNetworks = $moreover['branch_name'][$ckey];
//							if ($curNetworks['title'] != $network[$data['title']]['network_name']) {
//								$this->setError($curNetworks['coordinate'], '当前分行名称“' . $curNetworks['title'] . '” 与当前分行编号 “' . $data['title'] . '” 不匹配');
//							}
						}
					}
				}
			}
		}
	}

	/**
	 * 根据数据库中的数据检测正式数据，如规范的题目形式
	 * @param type $checkData
	 * @return type
	 */
	public function checkFormal($checkData) {
		if (!$checkData) {
			$this->setError('warning', '未获取到正式数据信息');
		}
		
		foreach ($checkData as $subNum => $datas) {
			$subTable = $this->subjectTable[$subNum];
			$relations = array();
			if (!isset($this->subjectTable[$subNum])) {
				$this->setError($cdata['data_th']['coordinate'], '当前题目编号“' . $subNum . '” 在所在问卷中未能找到，请仔细检查题目编号');
				continue;
			}
			
			// 映射数据库字段
			foreach ($datas['data'] as $exRow => $cdata) {
				/**
				 * 20150515需求，情景描述中存在链接地址的，需要转化为可以点击的链接
				 * 匹配【情景描述】中的链接，因为链接后面可能存在中文，因此如果链接地址存在于某个字符串中间的话，为了匹配正确，最好用空格隔开，如果不隔开，会出现将链接地址后的字符也匹配到
				 */
				$checkDescTitle = $cdata['th_check_desc']['title'];
				$host = "([a-z\d][-a-z\d]*[a-z\d]\.)+[a-z][-a-z\d]*[a-z]";
				$port = "(:\d{1,})?";
				$path = "(\/[^?<>\#\"\s]+)?";
				$query = "(\?[^<>\#\"\s]+)?";
				$regUrl = "#((ht|f)tps?:\/\/{$host}{$port}{$path}{$query})#i";
				if (preg_match_all($regUrl, $checkDescTitle, $matches)) {
					$url = $matches[0][0];
					$replaceUrl = '<a href="' . $url . '" target="_blank">' . $url . '</a>';
					$checkDescTitle = str_replace($url, $replaceUrl, $checkDescTitle);
				}
				
				$this->buffer[$exRow]['subject'][$subNum] = array(
					'subject_id' => $subTable['subject_id'],
					'answer_score' => $cdata['th_score']['title'],
					'answer_desc' => $checkDescTitle,
					'answer_option' => $cdata['th_option_content']['title'],
					'create_time' => time()
				);
				
//				// 保存选项信息
//				$oneOpt = $cdata['th_option_content']['title'];
//				if ($oneOpt) {
//					$exOptStr = str_replace('，', ',', $oneOpt);
//					$exOpt = explode(',', $exOptStr);
//					// 如果题目存在选项信息 和 填写了选项信息，才解析
//					if (!empty($subTable['option']) && !empty($exOpt)) {
//						foreach ($exOpt as $one) {
//							if (!in_array($one, array_keys($subTable['option']))) {
//								$this->setError($cdata['th_option_content']['coordinate'], 'excel中填写的选项 “' . join(',', $exOpt) . '” 和题目中规定的的选项序号（' . join(',', array_keys($subTable['option'])) . '）不符');
//								break;
//							}
//						}
//						$this->buffer[$exRow]['subject'][$subTable['subject_id']]['answer_option'] = join(',', $exOpt);
//					}
//				}
				
			}
			
			# 删除已经验证过的题目
			unset($this->subjectNames['formal'][$subNum]);
		}
		
	}

	/**
	 * 按照回答的题目类型，将excel数据归类整理
	 * @param type $excelData
	 * @return array
	 */
	public function induceData($excelData) {
		$tmpData = $excelData['data_th'];
		$thData = array_pop($tmpData);
		$titleInduce = $this->resolveTitles($thData); //解析回答的表头，分类标识
		
		//保存解析的表头数据
		$this->answerInduceTitle = $titleInduce;

		$return = array();

		# 以列为单位获取回答数据
		foreach ($titleInduce as $key => $data) {
			foreach ($excelData['data'] as $dataK => $dataD) { //回答data
				# 对应问卷基础信息题目
				if ($key == 'base') {
					foreach ($data as $baseK => $baseD) {
						$return[$key][$baseK]['data_th'] = $baseD;
						$return[$key][$baseK]['data'][$dataK] = $dataD[$baseK];
					}
					continue;
				}
				
				// 处理formal的题目
				foreach ($data as $subNum => $val) {
					foreach ($val as $exCol => $exD) {
						$exColName = array_search($exD['title'], self::$mustThField); 
						
						//计算出表头字段的数据库索引值
						if ($exColName) {
							$dataD[$exCol]['ex_col'] = $exCol;
							$return[$key][$subNum]['data_th'][$exCol] = $exD['title'];
							$return[$key][$subNum]['data'][$dataK][$exColName] = $dataD[$exCol];
						}
					}
				}
				
			}
		}
		
		return $return;
	}

	/**
	 * 解析回答的表头信息，按照题目分类
	 * @param type $thData
	 * @return array
	 */
	public function resolveTitles($thData) {
		$induceTitle = array();
		$tmp = $this->subjectNames;

		$repeat = array(
			'base' => array(),
			'formal' => array()
		);
		foreach ($thData as $key => $value) {
			$data = $value['title'];
			
			// 其他规范的题目
			foreach ($tmp as $strKey => $strData) {
				//base
				if ($strKey == 'base') {
					//判断是否存在重复的题目
					if (in_array($data, $repeat[$strKey])) {
						$this->setError($value['coordinate'], "当前的题目编号或者题目标题“" . $data . "”，已经存在相同的编号和标题了");
					}

					$subjkey = array_search($data, $strData);
					if (false !== $subjkey) {
						$induceTitle[$strKey][$key] = array(
							'title' => $data,
							'coordinate' => $value['coordinate']
						);
						$repeat[$strKey][] = $data;
						unset($tmp[$strKey][$subjkey]);
					}
					continue;
				}
				
				//判断是否存在重复的题目
				if (in_array($data, $repeat[$strKey])) {
					$this->setError($value['coordinate'], "当前的题目编号或者题目标题“" . $data . "”，已经存在相同的编号和标题了");
				}
				
				//formal的题目【解析formal，解析算法：使用self::$mustThField中的键值来分割excel中的表头，分割后相同的题目归为一类】
				foreach (self::$mustThField as $thCn) {
					list($subFKey, $subSuf) = explode($thCn, $data);
					if (array_key_exists($subFKey, $strData)) {
						$induceTitle[$strKey][$subFKey][$key] = array(
							'title' => $thCn,
							'coordinate' => $value['coordinate']
						);
						$repeat[$strKey][$subFKey] = $subFKey;
					}
				}
				
			}
		}

		//判断问卷中的题目是否全部回答
		foreach ($tmp as $item => $subs) {
			if (count($subs)) {
				foreach ($subs as $i => $v) {
					if ($item == 'base') {
						$this->setError('Err_No.' . $i, "问卷中的基础信息题目（" . $i . "--" . $v . "）在回答中未能找到对应的答案，请检查<br><b style='font-weight:normal;color:#9B0067'>（注意：为了减少问卷解析错误，即使问卷中不回答某个题目，也要在问卷中填写此题目的题目编号，对应题目的回答可以为空）</b>");
					} else {
						if (!in_array($i, $repeat[$item])) {
							$this->setError('Err_No.' . $i, "问卷中的题目（" . $i . "--" . $v . "）在回答中未能找到对应的答案，请检查<br><b style='font-weight:normal;color:#9B0067'>（注意：为了减少问卷解析错误，即使问卷中不回答某个题目，也要在问卷中填写此题目的题目编号，对应题目的回答可以为空）</b>");
						}
					}
				}
			}
		}
		
		return $induceTitle;
	}

	/**
	 * 对数据库中的数据进行分类，方便题型的检测
	 * @return array
	 */
	private function subjectCate() {
		$subjectNames = array();
		if (!$this->subjectTable) {
			return $subjectNames;
		}
		$tmp = array();
		foreach ($this->subjectTable as $item) {
			if ($item['is_basic']) {
				$subjectNames['base'][$item['question_num']] = $item['title'];
			} else {
				$subjectNames['formal'][$item['question_num']] = $item['title'];
			}
		}
		//分离【例行检查】 和 【好的地方】
		foreach ($subjectNames['formal'] as $num => $title) {
			preg_match_all('/^(ET_\d+)_(\d+)$/i', $num, $matches);
			if ($matches[0][0]) {
				$subjectNames['ext'][$matches[1][0]][$matches[2][0]] = $title;
				unset($subjectNames['formal'][$num]);
			}
		}

		return $subjectNames;
	}

}
